use NaturalKeySpike;

if exists (select * from sys.tables where name = 'TableD') drop table TableD;
if exists (select * from sys.tables where name = 'TableC') drop table TableC;
if exists (select * from sys.tables where name = 'TableB') drop table TableB;
if exists (select * from sys.tables where name = 'TableA') drop table TableA;

create table TableA (
    TableAId int not null primary key,
    Name nvarchar(255) not null,
    ModifiedOn datetime not null
);

create table TableB (
    TableAId int not null foreign key references TableA (TableAId),
    TableBId int not null,
    Name nvarchar(255) not null,
    ModifiedOn datetime not null,
    primary key (TableAId, TableBId)
);

create table TableC (
    TableAId int not null foreign key references TableA (TableAId),
    TableCId int not null,
    Name nvarchar(255) not null,
    ModifiedOn datetime not null,
    primary key (TableAId, TableCId)
);

create table TableD (
    TableAId int not null foreign key references TableA (TableAId),
    TableBId int not null,
    TableCId int not null,
    TableDId int not null,
    Name nvarchar(255) not null,
    ModifiedOn datetime not null,
    primary key (TableAId, TableBId, TableCId, TableDId),
    foreign key (TableAId, TableBId) references TableB (TableAId, TableBId),
    foreign key (TableAId, TableCId) references TableC (TableAId, TableCId)
);


return;

-------------------------------------------------------------------------------
-------------------------------------------------------------------------------

use NaturalKeySpike;
delete tabled;
delete tablec;
delete tableb;
delete tablea;
